//<my-matriculator - a simple tool for registering racers in a USAC cyclocross race>
//Copyright (C) <2011>  <Steven Rivers>

//This program is free software: you can redistribute it and/or modify
//it under the terms of the GNU General Public License as published by
//the Free Software Foundation, either version 3 of the License, or
//(at your option) any later version.

//This program is distributed in the hope that it will be useful,
//but WITHOUT ANY WARRANTY; without even the implied warranty of
//MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//GNU General Public License for more details.

//You should have received a copy of the GNU General Public License
//along with this program.  If not, see <http://www.gnu.org/licenses/>.

using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using Registration.Model.Entities;

namespace Registration.Model
{
    class EntryDb
    {
        public static IList<Entry> FindRider(string search)
        {
            if (search.Contains("cats:"))
                return FindByCategories(search);


            var s = search.ToLower();
            var racers = GetAllEntries();
            var matches = from rider in racers
                          where rider.Bib.ToString().Contains(s) ||
                                rider.LastName.ToLower().Contains(s) || 
                                rider.FirstName.ToLower().Contains(s) || 
   
                             rider.License.ToString().Contains(s) ||
                                rider.CategoryName.ToLower().Contains(s)
                          select rider;
            return matches.ToList();
        }

        private static IList<Entry> FindByCategories(string search)
        {
            search = search.ToLower();
            var idx = search.IndexOf(':');
            search = search.Substring(idx+1);
            search = search.Trim();
            if (string.IsNullOrEmpty(search))
                return new List<Entry>();

            var cats = search.Split(',').ToList();

            var racers = GetAllEntries();
            var matches = from rider in racers
                          where cats.Contains(rider.CategoryName.ToLower())
                          select rider;
            return matches.ToList();


        }


        public static ICollection<Entry> GetAllEntries()
        {
            var ds = EntryDbDataset.ReadDataSet();
            var entries = new ObservableCollection<Entry>();
            foreach (var e in from DataRow row in ds.Tables["Entry"].Rows select 
                                                                              new Entry((int)row["EntryID"], (int)row["Bib"], (int)row["License"], 
                                                                                        (int)row["CategoryId"], (string)row["CategoryName"], (string)row["FirstName"], 
                                                                                        (string)row["LastName"], (string)row["Club"], (string)row["Gender"], (string)row["Age"]))
            {
                entries.Add(e);
            }
            return entries;
        }

        private static void PopulateEntry(Entry theEntry, DataRow row)
        {
            row.SetField("Bib", theEntry.Bib);
            row.SetField("License", theEntry.License);
            row.SetField("CategoryId", theEntry.Category);
            row.SetField("CategoryName", theEntry.CategoryName);
            row.SetField("FirstName", theEntry.FirstName);
            row.SetField("LastName", theEntry.LastName);
            row.SetField("Club", theEntry.Club);
            row.SetField("Gender", theEntry.Gender);
            row.SetField("Age", theEntry.Age);
        }

        public static void UpdateEntry(Entry theEntry)
        {
            var ds = EntryDbDataset.ReadDataSet();
            var expression = "EntryID = " + theEntry.EntryId.ToString();
            var row = ds.Tables["Entry"].Select(expression)[0];
            row.BeginEdit();
            PopulateEntry(theEntry, row);
            row.AcceptChanges();
            ds.AcceptChanges();
            EntryDbDataset.WriteDataSet(ds);
        }

        public static void AddEntry(Entry theEntry)
        {
            var ds = EntryDbDataset.ReadDataSet();
            var row = ds.Tables["Entry"].NewRow();
            PopulateEntry(theEntry, row);
            ds.Tables["Entry"].Rows.Add(row);
            EntryDbDataset.WriteDataSet(ds);
        }

        public static Entry FindEntryByBib(int bib, int fieldid)
        {
            var entries = GetAllEntriesForField(fieldid);
            var matches = from e in entries where e.Bib == bib select e;
            return matches.FirstOrDefault();
        }

        public static IList<Entry> GetEntriesForBib(int bib)
        {
            var entries = GetAllEntries();
            var matches = from e in entries where e.Bib == bib select e;
            return matches.ToList();
        }

        public static void DeleteEntry(Entry entry)
        {
            var ds = EntryDbDataset.ReadDataSet();
            var expression = "EntryID = " + entry.EntryId.ToString();
            ds.Tables["Entry"].Select(expression)[0].Delete();
            ds.AcceptChanges();
            EntryDbDataset.WriteDataSet(ds);
        }

        public static IList<Entry> GetAllEntriesForField(int field, bool sortbycat=false)
        {
            var cats = FieldsDb.GetCategotyNamesForField(field);
            var entries = GetAllEntries();
            var matches = from entry in entries
                          where cats.Contains(entry.CategoryName)
                            select entry;

            return sortbycat ? matches.OrderBy(e => e.CategoryName).ToList() : matches.ToList();
        }

        public static void WriteEntriesToCsv(string filename)
        {
            var sb = new StringBuilder();
            var dt = EntryDbDataset.ReadDataSet().Tables["Entry"];

            var columnNames = dt.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToArray();
            sb.AppendLine(string.Join(",", columnNames));

            foreach (var fields in from DataRow row in dt.Rows select row.ItemArray.Select(field => field.ToString()).ToArray())
            {
                sb.AppendLine(string.Join(",", fields));
            }
            File.WriteAllText(filename, sb.ToString());
        }
    }
}